From 426f5802dd23db350723454fec7c401507fe972b Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Tue, 4 Aug 2026 17:15:27 +0000 Subject: [PATCH] Replace SIGHTING_RECORDS SOKWEDB special value with per-table values --- .../functions/create/build_swelling_states.m4 | 6 +++--- .../sokwedb/tables/create/swelling_sources.m4 | 8 ++++++-- doc/src/code_tables.m4 | 14 +++++++++++--- doc/src/functions/build_swelling_states.m4 | 6 ++++++ doc/src/tables/non_brec_sighting_sources.m4 | 2 -- doc/src/tables/swelling_sources.m4 | 2 -- include/global_constants.m4 | 6 ++++-- 7 files changed, 30 insertions(+), 14 deletions(-) diff --git a/db/schemas/sokwedb/functions/create/build_swelling_states.m4 b/db/schemas/sokwedb/functions/create/build_swelling_states.m4 index 2a4acc5..10a2765 100644 --- a/db/schemas/sokwedb/functions/create/build_swelling_states.m4 +++ b/db/schemas/sokwedb/functions/create/build_swelling_states.m4 @@ -152,7 +152,7 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) -- Collect all the swellings and their sources -- (except for manual sources) (SELECT arrivals_a.swelling, arrivals_a.swelling - , 'sdb_sokwedb_source' + , 'sdb_arrivals_a_source' FROM obs JOIN arrivals_a ON (arrivals_a.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) @@ -162,7 +162,7 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) AND cycle_states.ssrank IS NOT NULL UNION SELECT arrivals.cycle, arrivals.cycle - , 'sdb_sokwedb_source' + , 'sdb_arrivals_source' FROM obs JOIN arrivals ON (arrivals.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) @@ -172,7 +172,7 @@ CREATE OR REPLACE FUNCTION build_swelling_states(a_animid TEXT) AND cycle_states.ssrank IS NOT NULL UNION SELECT matings.swelling, matings.swelling - , 'sdb_sokwedb_source' + , 'sdb_matings_source' FROM obs JOIN matings ON (matings.eid = obs.eid) JOIN roles ON (roles.eid = obs.eid) diff --git a/db/schemas/sokwedb/tables/create/swelling_sources.m4 b/db/schemas/sokwedb/tables/create/swelling_sources.m4 index dd157d3..a3d9413 100644 --- a/db/schemas/sokwedb/tables/create/swelling_sources.m4 +++ b/db/schemas/sokwedb/tables/create/swelling_sources.m4 @@ -27,8 +27,12 @@ CREATE TABLE swelling_sources ( key_column(`SWELLING_SOURCES', `ID', INTEGER) ,source TEXT NOT NULL REFERENCES sighting_records - CONSTRAINT "Source may not be sdb_sokwedb_source" - CHECK(source <> 'sdb_sokwedb_source') + CONSTRAINT "Source may not be sdb_arrivals_a_source" + CHECK(source <> 'sdb_sdb_arrivals_a_source') + CONSTRAINT "Source may not be sdb_arrivals_source" + CHECK(source <> 'sdb_sdb_arrivals_source') + CONSTRAINT "Source may not be sdb_mating_source" + CHECK(source <> 'sdb_sdb_mating_source') ,date DATE NOT NULL ,animid_type_column(`animid', `AnimID', `NOT NULL') ,swellingmin TEXT diff --git a/doc/src/code_tables.m4 b/doc/src/code_tables.m4 index 6b12182..31a46cd 100644 --- a/doc/src/code_tables.m4 +++ b/doc/src/code_tables.m4 @@ -1309,10 +1309,13 @@ Special Values The following |SIGHTING_RECORDS.Code| values are special: -``sdb_sokwedb_source`` +``sdb_arrivals_source`` Indicates that the sighting or sexual swelling measurement value is - due to data held in SokweDB (aside from the - |NON_BREC_SIGHTING_SOURCES| and |SWELLING_SOURCES| tables). + due to data held in the |ARRIVALS| table. + +``sdb_arrivals_a_source`` + Indicates that the sighting or sexual swelling measurement value is + due to data held in the |ARRIVALS_A| table. ``sdb_manual_source`` Indicates that the sexual swelling values given in the @@ -1322,6 +1325,11 @@ The following |SIGHTING_RECORDS.Code| values are special: This value has no special meaning when used in |NON_BREC_SIGHTING_SOURCES|. +``sdb_matings_source`` + Indicates that the sighting or sexual swelling measurement value is + due to data held in the |MATINGS| table. + + Column Descriptions ``````````````````` diff --git a/doc/src/functions/build_swelling_states.m4 b/doc/src/functions/build_swelling_states.m4 index 7d2714d..921d0aa 100644 --- a/doc/src/functions/build_swelling_states.m4 +++ b/doc/src/functions/build_swelling_states.m4 @@ -87,6 +87,12 @@ are: * |SWELLING_SOURCES|.\ |SWELLING_SOURCES.SwellingMin| +Each of |ARRIVALS_A|, |ARRIVALS|, and |MATINGS| should have a +corresponding special value in |SIGHTING_RECORDS|.\ [#f1]_ +This enables the "ranking" of swelling information to control the +selection of which source to use when identical swelling information +appears in more than one place. + When the minimum or maximum value appears in more than one of the above tables, the source recorded in |SWELLING_SOURCES|.\ |SWELLING_SOURCES.Source| is that with the smallest diff --git a/doc/src/tables/non_brec_sighting_sources.m4 b/doc/src/tables/non_brec_sighting_sources.m4 index 6f61c2c..138a871 100644 --- a/doc/src/tables/non_brec_sighting_sources.m4 +++ b/doc/src/tables/non_brec_sighting_sources.m4 @@ -64,8 +64,6 @@ Source A |SIGHTING_RECORDS|.\ |SIGHTING_RECORDS.Code| value. |NON_BREC_SIGHTING_SOURCES.Source_summary| -The special value ``sdb_sokwedb_source`` may not appear as a value in -this column. |notnull| diff --git a/doc/src/tables/swelling_sources.m4 b/doc/src/tables/swelling_sources.m4 index 2e09988..9071500 100644 --- a/doc/src/tables/swelling_sources.m4 +++ b/doc/src/tables/swelling_sources.m4 @@ -99,8 +99,6 @@ Source A |SIGHTING_RECORDS|.\ |SIGHTING_RECORDS.Code| value. |SWELLING_SOURCES.Source_summary| -The special value ``sdb_sokwedb_source`` may not appear as a value in -this column. |notnull| diff --git a/include/global_constants.m4 b/include/global_constants.m4 index d687cbb..d1699b8 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -182,8 +182,10 @@ define(`sdb_unkpair', `UNKPair') dnl dnl SIGHTING_RECORDS dnl -dnl Value used when the SokweDB tables indicate a sighting -define(`sdb_sokwedb_source', `SOKWEDB') +dnl Values used when the SokweDB tables indicate a sighting +define(`sdb_arrivals_a_source', `ARRIVALS_A') +define(`sdb_arrivals_source', `ARRIVALS') +define(`sdb_matings_source', `MATINGS') dnl Value used to manually override all other sexual swelling values define(`sdb_manual_source', `MANUAL') -- 2.34.1